Memory Cognizant Query Optimization

ABSTRACT Complex queries make heavy use of join, aggregation and sorting operations and these operations are memory intensive. Typi al optimizers assume all the memory to be available to ea h operator in the query tree. But while exe uting pipelines memory will get divided amongst all the operators running simultaneously in a pipeline. The ost of an operator generally depends on the available memory. If the memory allo ated to an operator is less than what an optimizer assumes, ost estimated by the optimizer would be wrong. Thus the query optimization and memory distribution are interdependent and if done separately may not yield best results. The query optimizer should not only onsider the total memory available but should also de ide how to divide it optimally among the operators of the plan. We show how to optimize a query given the ost versus memory allo ation fun tion for ea h operator. We have extended the Vol ano optimizer to make it memory ognizant. Part of the job of the optimizer is to de ide whi h edge to pipeline and whi h edge to blo k. A pipelinable edge an be broken (i.e. onverted) into a blo king edge. But the de ision to break a pipelinable edge depends upon whether the extra memory available to individual pipelinable trees thus formed an more than o set the extra disk IO of the intermediate results. This de ision is integrated into our memory ognizant optimizer.